﻿    CREATE OR REPLACE FUNCTION get_student(
        p_nationality_id VARCHAR(36) DEFAULT NULL) 
    RETURNS 
    SETOF student 
    AS
    $$
        /***************************************************************************************************
    **  Function:	public.get_student
    **  Author:		Zhixian Ong
    **
    **  Description:
    **    
    **      Get student with specified nationality id.
    **
    **  Parameters (1):
    **
    **      name    VARCHAR(128)    - name of student to find
    **
    **  Return:
    **
    **      student
    **
    **  Sample call:
    **
    **      Find student name 
    **      SELECT * FROM get_student('S000000Z');
    **      SELECT * FROM get_student('');
    **      SELECT * FROM get_student();
    **      SELECT * FROM get_student('zxc');
    **
    **
    **  Modification History:
    **
    **      20140102  Zhixian Ong     - Initial prototype
    ** 
    **  Status:
    **
    **      Dev
    ** 
    ***************************************************************************************************/
    DECLARE
        r student;
        
    BEGIN
        
        --------------------------------------------------------------------------------
        -- Return result
        --------------------------------------------------------------------------------
        RETURN QUERY
        SELECT  * 
        FROM    student 
        WHERE   (p_nationality_id IS NOT NULL)
                AND
                (nationality_id = p_nationality_id)
        LIMIT   1;
        
    END;
    $$ LANGUAGE plpgsql;